package com.junzhiit.nengji.cms.web.util.excel;

import java.awt.image.BufferedImage;
import java.io.ByteArrayOutputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.InputStream;
import java.io.PushbackInputStream;
import java.lang.reflect.Method;
import java.text.DecimalFormat;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.UUID;

import javax.imageio.ImageIO;
import javax.servlet.http.HttpServletRequest;

import org.apache.commons.io.FileUtils;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.POIXMLDocument;
import org.apache.poi.hssf.usermodel.HSSFClientAnchor;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.openxml4j.opc.OPCPackage;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.ClientAnchor;
import org.apache.poi.ss.usermodel.DataFormat;
import org.apache.poi.ss.usermodel.Drawing;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFClientAnchor;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import com.bainaeco.framework.base.spring.Context;
import com.bainaeco.framework.util.PropertiesUtil;
import com.junzhiit.nengji.cms.web.util.Constants;
import com.junzhiit.nengji.cms.web.util.FileUtil;
import com.junzhiit.nengji.cms.web.util.ReleaseResourceUtil;
import com.junzhiit.nengji.cms.web.util.excel.enums.WorkbookType;

import net.coobird.thumbnailator.Thumbnails;
import net.coobird.thumbnailator.geometry.Positions;

public class ViewExcel extends Context {

	public String saveExcelDocument(Workbook workbook, String path, String xlsFileName, HttpServletRequest request,
			Integer fileType) {
		String filefullPath = "";
		FileOutputStream fos = null;
		try {
			// byte[] bytes = xlsFileName.getBytes("UTF-8");
			// String fileName = new String(bytes, "ISO-8859-1");

			/*
			 * String agent = request.getHeader("USER-AGENT"); if (null != agent
			 * && -1 != agent.indexOf("MSIE") || null != agent && -1 !=
			 * agent.indexOf("Trident")) {//IE xlsFileName =
			 * java.net.URLEncoder.encode(xlsFileName, "UTF-8"); }
			 */
			File fileFolder = new File(path);
			if (!fileFolder.exists()) {
				fileFolder.mkdirs();
			}
			if (fileType != null && fileType == WorkbookType.XSSFWORKBOOK.toIntValue()) {
				filefullPath = path + xlsFileName + ".xlsx";
			} else {
				filefullPath = path + xlsFileName + ".xls";
			}

			fos = new FileOutputStream(filefullPath);
			workbook.write(fos);
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			ReleaseResourceUtil.closeFileOutputStream(fos);
		}
		return filefullPath;
	}

	public HSSFWorkbook resultSetToExcel(List<ExcelElement> lstTitle, List<?> lstData, Class<?> c, boolean isShowTotal)
			throws Exception {
		HSSFWorkbook workbook = new HSSFWorkbook();
		HSSFSheet sheet = workbook.createSheet();

		ViewExcel ve = new ViewExcel();
		return (HSSFWorkbook) ve.getWorkbook(workbook, sheet, lstTitle, lstData, c, isShowTotal,
				WorkbookType.HSSFWORKBOOK.toIntValue(), 0, true, true);
	}

	public HSSFWorkbook resultMergeSetToExcel(List<ExcelElement> lstTitle, List<?> lstData, Class<?> c,
			boolean isShowTotal, int mgLength) throws Exception {
		HSSFWorkbook workbook = new HSSFWorkbook();
		HSSFSheet sheet = workbook.createSheet();

		ViewExcel ve = new ViewExcel();
		return (HSSFWorkbook) ve.getMergeWorkbook(workbook, sheet, lstTitle, lstData, c, isShowTotal,
				WorkbookType.HSSFWORKBOOK.toIntValue(), 0, true, true, mgLength);
	}

	public XSSFWorkbook resultSetToExcelXlsx(List<ExcelElement> lstTitle, List<?> lstData, Class<?> c,
			boolean isShowTotal) throws Exception {
		XSSFWorkbook workbook = new XSSFWorkbook();
		XSSFSheet sheet = workbook.createSheet();
		ViewExcel ve = new ViewExcel();
		return (XSSFWorkbook) ve.getWorkbook(workbook, sheet, lstTitle, lstData, c, isShowTotal,
				WorkbookType.XSSFWORKBOOK.toIntValue(), 0, true, true);
	}

	public WorkbookEdit resultEditToExcelXlsx(String resourcesPath, List<ExcelElement> lstTitle, List<?> lstData,
			Class<?> c, boolean isShowTotal, Integer iRow, Boolean wrapText) throws Exception {
		InputStream fs = new FileInputStream(resourcesPath);
		Workbook workbook = ViewExcel.newWorkbook(fs);
		Sheet sheet = workbook.getSheetAt(0);
		int type = ViewExcel.getType(new FileInputStream(resourcesPath));
		ViewExcel ve = new ViewExcel();

		WorkbookEdit workbookEdit = new WorkbookEdit();
		workbookEdit.setType(type);
		workbookEdit.setWorkbook(
				ve.getWorkbook(workbook, sheet, lstTitle, lstData, c, isShowTotal, type, iRow, false, wrapText));
		return workbookEdit;

	}

	public WorkbookEdit update(WorkbookEdit workbookEdit, List<EditExcel> list) {
		Workbook workbook = workbookEdit.getWorkbook();
		Sheet sheet = workbook.getSheetAt(0);
		Row row = null;
		Cell cell = null;
		for (EditExcel editExcel : list) {
			row = sheet.getRow(editExcel.getRow());
			cell = row.getCell(editExcel.getCell());
			cell.setCellType(Cell.CELL_TYPE_STRING);
			cell.setCellValue(editExcel.getValue().toString());
		}
		workbookEdit.setWorkbook(workbook);
		return workbookEdit;
	}

	public static Workbook newWorkbook(InputStream inp) {
		try {
			if (!inp.markSupported()) {
				inp = new PushbackInputStream(inp, 8);
			}

			if (POIFSFileSystem.hasPOIFSHeader(inp)) {
				return new HSSFWorkbook(inp);
			}
			if (POIXMLDocument.hasOOXMLHeader(inp)) {
				return new XSSFWorkbook(OPCPackage.open(inp));
			}
			return null;
		} catch (Exception e) {
			e.printStackTrace();
			return null;
		}
	}

	private static int getType(InputStream inp) {
		int type = WorkbookType.HSSFWORKBOOK.toIntValue();
		try {
			if (!inp.markSupported()) {
				inp = new PushbackInputStream(inp, 8);
			}
			if (POIFSFileSystem.hasPOIFSHeader(inp)) {
				type = WorkbookType.HSSFWORKBOOK.toIntValue();
			}
			if (POIXMLDocument.hasOOXMLHeader(inp)) {
				type = WorkbookType.XSSFWORKBOOK.toIntValue();
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return type;
	}

	private Workbook getWorkbook(Workbook workbook, Sheet sheet, List<ExcelElement> lstTitle, List<?> lstData,
			Class<?> c, boolean isShowTotal, Integer workbookType, Integer iRow, Boolean isCreateTitle,
			Boolean wrapText) throws Exception {

		DecimalFormat df = new DecimalFormat("#0.00");

		CellStyle cellStyle = workbook.createCellStyle();
		// 指定单元格居中对齐
		cellStyle.setAlignment(CellStyle.ALIGN_CENTER);
		// 指定单元格垂直居中对齐
		cellStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
		// 指定当单元格内容显示不下时自动换行
		cellStyle.setWrapText(wrapText);
		DataFormat format = workbook.createDataFormat();
		cellStyle.setDataFormat(format.getFormat("@"));// 设为文本格式

		// 设置单元格字体
		// Font font = workbook.createFont();
		// font.setBoldweight(Font.BOLDWEIGHT_BOLD);
		// font.setFontName("宋体");
		// font.setFontHeight((short) 200);
		// cellStyleTitle.setFont(font);

		// 背景色
		// style.setFillForegroundColor(HSSFColor.YELLOW.index);
		// style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
		// style.setFillBackgroundColor(HSSFColor.YELLOW.index);

		// 设置边框
		// cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
		// cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
		// cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
		// cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
		// cellStyle.setTopBorderColor(HSSFColor.BLACK.index);
		// cellStyle.setRightBorderColor(HSSFColor.BLACK.index);
		// cellStyle.setBottomBorderColor(HSSFColor.BLACK.index);
		// cellStyle.setLeftBorderColor(HSSFColor.BLACK.index);

		if (iRow == null) {
			iRow = sheet.getLastRowNum();
		}
		Row row = sheet.createRow(iRow);
		Cell cell = null;

		if (isCreateTitle) {
			for (int i = 0; i < lstTitle.size(); i++) {
				cell = row.createCell((i));
				cell.setCellType(Cell.CELL_TYPE_STRING);
				cell.setCellValue(lstTitle.get(i).getTitle());
				cell.setCellStyle(cellStyle);
				sheet.setColumnWidth(i, lstTitle.get(i).getWidth() * 256);
			}
			iRow++;
		}

		Object countReport = c.newInstance();
		Drawing patriarch = sheet.createDrawingPatriarch();// 图片
		for (int i = 0; i < lstData.size(); i++) {
			Object m = lstData.get(i);
			row = sheet.createRow(iRow);
			if (m == null) {
				// 四个参数分别是：起始行，结束行,起始列，结束列
				sheet.addMergedRegion(new CellRangeAddress(iRow, iRow, 0, lstTitle.size()));
				row.setHeightInPoints(30);
				iRow++;
				continue;
			}

			for (int j = 0; j < lstTitle.size(); j++) {

				cell = row.createCell(j);
				cell.setCellStyle(cellStyle);
				String field = lstTitle.get(j).getField();

				field = Character.toUpperCase(field.charAt(0)) + field.substring(1);
				String getField = "get" + field;

				// 不支持父类的方法调用。
				Method getMethod = c.getDeclaredMethod(getField);
				Object oj = getMethod.invoke(m);

				if (oj == null) {
					oj = "";
				}

				if (lstTitle.get(j).getIsImage() == true && StringUtils.isNotBlank(oj.toString())) {
					// 得到user的图片 oj.toString()
					String imageName = oj.toString();
					File file = new File(imageName);
					if (file.exists()) {
						String imgSuffixName = imageName.substring(imageName.lastIndexOf(".") + 1);// 获得文件后缀名，获得图片类型

						/**
						 * 为了把周边边白 watermark(位置，水印图，透明度)
						 */
						String uploadTempPath = PropertiesUtil.getProperties(Constants.WEB_CONFIGURE,
								Constants.UPLOAD_TEMP_PATH);
						String tempImage = uploadTempPath + "/" + UUID.randomUUID().toString() + "." + imgSuffixName;

						Thumbnails.of(imageName).size(320, 320).toFile(tempImage);// 把图片压缩为指定大小，然后放到临时文件夹中

						// 设置白色背景图
						Thumbnails.of(FileUtil.createWhiteImge(Constants.DATA_WHITE_PNG, imgSuffixName, 370, 370))
								.size(370, 370).watermark(Positions.CENTER, ImageIO.read(new File(tempImage)), 1f)
								.outputQuality(1f).toFile(tempImage);

						row.setHeightInPoints(72);

						ByteArrayOutputStream byteArrayOut = new ByteArrayOutputStream();
						BufferedImage image = ImageIO.read(new File(tempImage));

						ImageIO.write(image, imgSuffixName, byteArrayOut);

						ClientAnchor anchor = null;

						if (workbookType == WorkbookType.HSSFWORKBOOK.toIntValue()) {
							anchor = new HSSFClientAnchor(10, 10, 0, 0, (short) j, iRow, (short) (j + 1), (iRow + 1));
						} else {
							anchor = new XSSFClientAnchor(100, 100, 100, 100, j, iRow, j + 1, iRow + 1);
						}
						anchor.setAnchorType(0);
						if (imgSuffixName.equals("jpg")) {
							patriarch.createPicture(anchor,
									workbook.addPicture(byteArrayOut.toByteArray(), Workbook.PICTURE_TYPE_JPEG));
						} else {
							patriarch.createPicture(anchor,
									workbook.addPicture(byteArrayOut.toByteArray(), Workbook.PICTURE_TYPE_PNG));
						}

						// 删除临时图片
						File targetFile = new File(tempImage);
						if (targetFile.isDirectory()) {
							FileUtils.deleteDirectory(targetFile);
						} else if (targetFile.isFile()) {
							targetFile.delete();
						}

					} else {
						cell.setCellType(Cell.CELL_TYPE_STRING);
						cell.setCellValue(oj.toString());
					}
				} else {
					cell.setCellType(Cell.CELL_TYPE_STRING);
					cell.setCellValue(oj.toString());
					// count total
					if (lstTitle.get(j).getIsCount() == true) {
						String setField = "set" + field;

						if (oj instanceof Double) {
							Method setMethod = c.getDeclaredMethod(setField, Double.class);
							Double dOj = Double.valueOf(oj.toString());
							if (getMethod.invoke(countReport) == null) {
								setMethod.invoke(countReport, dOj);
							} else {
								Double dCountOj = Double.valueOf(getMethod.invoke(countReport).toString());
								setMethod.invoke(countReport, dOj + dCountOj);
							}
						} else if (oj instanceof Integer) {
							Method setMethod = c.getDeclaredMethod(setField, Integer.class);
							Integer iOj = Integer.valueOf(oj.toString());
							if (getMethod.invoke(countReport) == null) {
								setMethod.invoke(countReport, iOj);
							} else {
								Integer iCountOj = Integer.valueOf(getMethod.invoke(countReport).toString());
								setMethod.invoke(countReport, iOj + iCountOj);
							}
						}
					}
				}

			}
			iRow++;
		}
		// show count

		if (isShowTotal) {
			row = sheet.createRow(iRow);
			cell = row.createCell(0);
			cell.setCellType(Cell.CELL_TYPE_STRING);
			cell.setCellValue("Total");
		}

		for (int i = 0; i < lstTitle.size(); i++) {
			if (lstTitle.get(i).getIsCount() == true) {

				cell = row.createCell(i);
				cell.setCellType(Cell.CELL_TYPE_STRING);

				String field = lstTitle.get(i).getField();

				field = Character.toUpperCase(field.charAt(0)) + field.substring(1);
				String getField = "get" + field;
				Method getMethod = c.getDeclaredMethod(getField);

				Object obj = getMethod.invoke(countReport);

				if (obj == null) {
					obj = "";
				}
				String value = obj.toString().indexOf(".") != -1 ? df.format(obj).toString() : obj.toString();
				cell.setCellValue(value);
			}
		}
		return workbook;
	}

	private Workbook getMergeWorkbook(Workbook workbook, Sheet sheet, List<ExcelElement> lstTitle, List<?> lstData,
			Class<?> c, boolean isShowTotal, Integer workbookType, Integer iRow, Boolean isCreateTitle,
			Boolean wrapText, int mgLength) throws Exception {

		CellStyle cellStyle = workbook.createCellStyle();
		// 指定单元格居中对齐
		cellStyle.setAlignment(CellStyle.ALIGN_CENTER);
		// 指定单元格垂直居中对齐
		cellStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
		// 指定当单元格内容显示不下时自动换行
		cellStyle.setWrapText(wrapText);
		DataFormat format = workbook.createDataFormat();
		cellStyle.setDataFormat(format.getFormat("@"));// 设为文本格式

		if (iRow == null) {
			iRow = sheet.getLastRowNum();
		}
		Row row = sheet.createRow(iRow);
		Cell cell = null;
		if (isCreateTitle) {
			for (int i = 0; i < lstTitle.size(); i++) {
				cell = row.createCell((i));
				cell.setCellStyle(cellStyle);
				cell.setCellType(Cell.CELL_TYPE_STRING);
				cell.setCellValue(lstTitle.get(i).getTitle());
				sheet.setColumnWidth(i, lstTitle.get(i).getWidth() * 256);
			}
			iRow++;
		}

		Map<String, String> map = new HashMap<String, String>();
		int mergeCount = 0;
		for (int i = 0; i < lstData.size(); i++) {
			Object m = lstData.get(i);
			if (m == null) {
				// 四个参数分别是：起始行，结束行,起始列，结束列
				sheet.addMergedRegion(new CellRangeAddress(iRow, iRow, 0, lstTitle.size()));
				row.setHeightInPoints(30);
				iRow++;
				continue;
			}
			Method getId = c.getDeclaredMethod("getId");
			String id = (String) getId.invoke(m);
			if (map.containsKey(id)) {
				mergeCount++;
			} else if (mergeCount > 0) {
				for (int k = 0; k < mgLength; k++) {
					sheet.addMergedRegion(new CellRangeAddress(iRow - 1 - mergeCount, iRow - 1, k, k));
				}
				mergeCount = 0;
			}
			Method getOgId = c.getDeclaredMethod("getOrdersGoodsId");
			String ogId = (String) getOgId.invoke(m);
			map.put(id, ogId);

			int j;
			if (mergeCount > 0) {
				j = mgLength;
			} else {
				j = 0;
			}
			row = sheet.createRow(iRow);
			for (; j < lstTitle.size(); j++) {
				cell = row.createCell(j);
				cell.setCellStyle(cellStyle);
				String field = lstTitle.get(j).getField();
				field = Character.toUpperCase(field.charAt(0)) + field.substring(1);
				String getField = "get" + field;
				Method getMethod = c.getDeclaredMethod(getField);
				Object oj = getMethod.invoke(m);
				if (oj == null) {
					oj = "";
				}
				cell.setCellType(Cell.CELL_TYPE_STRING);
				cell.setCellValue(oj.toString());
			}
			iRow++;
		}
		return workbook;
	}
}
